using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Drawing;
using System.Data;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Configuration;
using System.Collections;
using System.Collections.Specialized;

namespace eBirdAttend
{
    public partial class SearchEmployee : UserControl
    {
        public string strCon = Properties.Settings.Default["ConString"].ToString();
        SqlConnection sqlCon = new SqlConnection();
        SqlCommand sqlCmd = new SqlCommand();

       
        public SearchEmployee()
        {
            InitializeComponent();
            sqlCon.ConnectionString = strCon;
        }

        // Function name   : btnSearch_Click
        // Description     : 
        // Return type     : void 
        // Author          : Priya Purushothaman
        // Date            : 10/19/2007
        // Modified By     : 
        // Modified On     
 
        // Purpose         : 
        // Argument        : object sender
        // Argument        : EventArgs e

        private void btnSearch_Click(object sender, EventArgs e)
        {
            try
            {
                
                dataGridView1.Visible = true;
                sqlCmd = new SqlCommand();
                string strQuery = "";
                string strCondition = " where";
                bool bFlag = false;

                //strQuery = "select empid,EmpFirstName,EmpLastName,EmpCmpId,EmpDesigId,EmpDeptId,EmpBrId," +
                //                     "EmpAddress,EmpPhone,EmpMobile,EmpDOB,EmpJoinDate,EmpResigDate," +
                //                     "EmpStatus,EmpEmail from employdetails ";
                strQuery = "select * from SearchEmpView";

                if (cmbStatus.SelectedIndex != 0)
                {
                    strCondition += " EmpStatus = ";

                    if (cmbStatus.SelectedIndex == 1)
                    {
                        strCondition += " 1 ";
                    }
                    else if (cmbStatus.SelectedIndex == 2)
                    {
                        strCondition += " 2 ";
                    }
                    else if (cmbStatus.SelectedIndex == 3)
                    {
                        strCondition += " 3 ";
                    }

                    bFlag = true;
                }
                if (cmbShift.SelectedIndex != 0)
                {
                    if (bFlag)
                    {
                        strCondition += " and ";
                    }
                    strCondition += " EmpShiftId = " + cmbShift.SelectedValue;

                    bFlag = true;
                }
                if (txtFirstName.Text != "")
                {
                    if (bFlag)
                    {
                        strCondition += " and ";
                    }
                    strCondition += " lower(FirstName) like '%" + txtFirstName.Text.ToLower() + "%'";
                    bFlag = true;
                }
                if (txtLastName.Text != "")
                {
                    if (bFlag)
                    {
                        strCondition += " and ";
                    }
                    strCondition += " lower(LastName) like '%" + txtLastName.Text.ToLower() + "%'";
                    bFlag = true;
                }
                if (comboBox1.SelectedIndex != 0)
                {
                    if (bFlag)
                    {
                        strCondition += " and ";
                    }

                    if (comboBox1.SelectedIndex == 1)
                    {
                        strCondition += " JoinDate between '" + dateJoining1.Text + "' and '" + dateJoining2.Text + "'";
                    }
                    else if (comboBox1.SelectedIndex == 2)
                    {
                        strCondition += " JoinDate <= '" + dateJoining1.Text + "'";
                    }
                    else
                    {
                        strCondition += " JoinDate >= '" + dateJoining1.Text + "'";
                    }

                    bFlag = true;
                }
                if (comboBox2.SelectedIndex != 0)
                {
                    if (bFlag)
                    {
                        strCondition += " and ";
                    }

                    if (comboBox2.SelectedIndex == 1)
                    {
                        strCondition += " ResignationDate between '" + dateResig1.Text + "' and '" + dateResig2.Text + "'";
                    }
                    else if (comboBox2.SelectedIndex == 2)
                    {
                        strCondition += " ResignationDate <= '" + dateResig1.Text + "'";
                    }
                    else
                    {
                        strCondition += "  ResignationDate >= '" + dateResig1.Text + "'";
                    }
                    bFlag = true;
                }

                if (bFlag)
                {
                    sqlCmd.CommandText = strQuery + strCondition;
                }
                else
                {
                    sqlCmd.CommandText = strQuery;
                }

                sqlCmd.Connection = sqlCon;
                sqlCon.Open();
                SqlDataAdapter sqlAdap = new SqlDataAdapter(sqlCmd);
                sqlCmd.ExecuteNonQuery();
                DataSet ds = new DataSet();
                sqlAdap.Fill(ds);

                
                dataGridView1.DataSource = ds;
                dataGridView1.DataMember = ds.Tables[0].ToString();
                dataGridView1.Columns[0].Visible = false;
                dataGridView1.Columns[17].Visible = false;
                dataGridView1.Columns[18].Visible = false;
                sqlCon.Close();
            }
            catch (Exception ex)
            {
                if (sqlCon.State == ConnectionState.Open)
                    sqlCon.Close();
            }
        }

        // Function name   : btnCancel_Click
        // Description     : 
        // Return type     : void 
        // Author          : Priya Purushothaman
        // Date            : 10/19/2007
        // Modified By     : 
        // Modified On     : 
        // Purpose         : 
        // Argument        : object sender
        // Argument        : EventArgs e

        private void btnCancel_Click(object sender, EventArgs e)
        {
            dataGridView1.Visible = false;
            ClearControls();
            groupBox2.Visible = false;
        }

        private void btnClose_Click(object sender, EventArgs e)
        {
            this.Dispose();
        }

        private void SearchEmployee_Load(object sender, EventArgs e)
        {
            


            employDetailsTableAdapter1.Fill(employeeDataset1.EmployDetails);

            string _strc = Properties.Settings.Default["ConnectionString"].ToString();
            shiftDetailsTableAdapter.Connection.ConnectionString = _strc;
            shiftDetailsTableAdapter.Fill(shiftDetailsDataset.ShiftDetails);
            DataTable table1 = new DataTable("Table1");
            table1 = shiftDetailsDataset.Tables[0].Clone();
            // Add some rows.
            DataRow row;
            row = table1.NewRow();
            row["shid"] = 0;
            row["shdesc"] = "select";
            row["shname"] = "[Select]";
            //row["ShMinTimeFrHalfDay"] = "1";
            //row["ShMinTimeFrFullDay"] = "1";
            table1.Rows.Add(row);
            // Accept changes.
            table1.AcceptChanges();
            shiftDetailsDataset.Tables[0].Constraints.Clear();
            DataTable table2 = shiftDetailsDataset.Tables[0];
            table1.Merge(table2);
            cmbShift.DataSource = table1;
            cmbShift.DisplayMember = "shname";
            cmbShift.ValueMember = "shid"; 

            comboBox1.SelectedIndex = 0;
            comboBox2.SelectedIndex = 0;
            cmbStatus.SelectedIndex = 0;
            cmbShift.SelectedIndex = 0;
            txtFirstName.Focus();

            ArrayList EmpStatus = new ArrayList();
                EmpStatus.Add(new AddValue("[Select]", 0));
                EmpStatus.Add(new AddValue("Active", 1));
                EmpStatus.Add(new AddValue("Resigned", 2));
                EmpStatus.Add(new AddValue("Suspended/Long Leave", 3));

                this.cmbEmpStatus.DataSource = EmpStatus;
                this.cmbEmpStatus.DisplayMember = "Display";
                this.cmbEmpStatus.ValueMember = "Value";

                cmbEmpStatus.SelectedIndex = 0;

                departmentDataset1.Clear();
                departmentTableAdapter1.Fill(departmentDataset1.Department);

                table1 = new DataTable("Table1");

                table1 = departmentDataset1.Tables[0].Clone();

                //Add some rows.
                //DataRow row;
                row = table1.NewRow();
                row["depid"] = 0;
                row["depname"] = "[Select]";
                row["depcmpid"] = "1";
                row["dephod"] = "Select";
                table1.Rows.Add(row);

                // Accept changes.
                table1.AcceptChanges();

                departmentDataset1.Tables[0].Constraints.Clear();
                table2 = departmentDataset1.Tables[0];

                table1.Merge(table2);

                cmbDepartmentID.DataSource = table1;
                cmbDepartmentID.DisplayMember = "depname";
                cmbDepartmentID.ValueMember = "depid";


                designationTableAdapter1.Fill(designationDataset1.Designation);
                table1 = new DataTable("Table1");

                table1 = designationDataset1.Tables[0].Clone();

                // Add some rows.

                row = table1.NewRow();
                row["desid"] = 0;
                row["desdesig"] = "[Select]";

                table1.Rows.Add(row);

                // Accept changes.
                table1.AcceptChanges();

                designationDataset1.Tables[0].Constraints.Clear();
                table2 = designationDataset1.Tables[0];
                table1.Merge(table2);

                cmbDesigId.DataSource = table1;
                cmbDesigId.DisplayMember = "desdesig";
                cmbDesigId.ValueMember = "desid";

                //companyTableAdapter1.Fill(companyDataset1.Company);

                branchTableAdapter1.Fill(branchDataset1.Branch);
                table1 = new DataTable("Table1");

                table1 = branchDataset1.Tables[0].Clone();

                // Add some rows.
                row = table1.NewRow();
                row["brbranchid"] = 0;
                row["brcmpid"] = 0;
                row["brname"] = "[Select]";

                table1.Rows.Add(row);

                // Accept changes.
                table1.AcceptChanges();

                branchDataset1.Tables[0].Constraints.Clear();
                table2 = branchDataset1.Tables[0];
                table1.Merge(table2);

                cmbBranchID.DataSource = table1;
                cmbBranchID.DisplayMember = "brname";
                cmbBranchID.ValueMember = "brbranchid";

        }


        // Function name   : ClearControls
        // Description     : 
        // Return type     : void 
        // Author          : Priya Purushothaman
        // Date            : 10/22/2007
        // Modified By     : 
        // Modified On     : 
        // Purpose         : 

        public void ClearControls()
        {
            txtFirstName.Text = "";
            txtLastName.Text = "";
            cmbStatus.SelectedIndex = 0;
            cmbShift.SelectedIndex = 0;
            dateJoining1.Text = DateTime.Today.ToShortDateString();
            dateJoining2.Text = DateTime.Today.ToShortDateString();
            dateResig1.Text = DateTime.Today.ToShortDateString();
            dateResig2.Text = DateTime.Today.ToShortDateString();
        }

        private void groupBox1_Enter(object sender, EventArgs e)
        {

        }

        private void dateJoining2_ValueChanged(object sender, EventArgs e)
        {

        }

        private void btnExport_Click(object sender, EventArgs e)
        {
            int j = 1;
            int i;

            string strReportTemplate = Application.StartupPath.ToString() + "\\" +
                 Properties.Settings.Default.EmployeeDetails.ToString();
            if (strReportTemplate == "")
            {
                MessageBox.Show("Report Template Name Missing");
                return;
            }

            StringCollection strHeaderCol = new StringCollection();
            EbExcelAuto exelAttnReport = new EbExcelAuto();
            string curColText = "";
            exelAttnReport.Init();
            exelAttnReport.OpenExcelFile(strReportTemplate);
            exelAttnReport.FillExcellSheetFromDatagrid("#START", dataGridView1, "Date");
            exelAttnReport.SaveAs(null);
            exelAttnReport.Close();
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            try
            {
                if (dataGridView1.SelectedRows.Count == 0)
                { MessageBox.Show("Please select a row for edit"); return; }

                if ((dataGridView1.RowCount - 1) == dataGridView1.SelectedRows[0].Index)
                {
                    MessageBox.Show("No content to edit");                    
                    ClearControls();
                    return;
                }
                ClearControls();
                dataGridView1.ReadOnly = true;
                groupBox2.Visible = true;

                txtEmpCode.Text = dataGridView1.SelectedRows[0].Cells[1].Value.ToString();
                //txtCardId.Text = empView.SelectedRows[0].Cells[1].Value.ToString();
                //_CardIDChd = txtCardId.Text;
                txtEmpId.Text = dataGridView1.SelectedRows[0].Cells[0].Value.ToString();
                txtFName.Text = dataGridView1.SelectedRows[0].Cells[2].Value.ToString();
                txtLName.Text = dataGridView1.SelectedRows[0].Cells[3].Value.ToString();
                txtCompanyId.Text = dataGridView1.SelectedRows[0].Cells[8].Value.ToString();
                cmbBranchID.ResetText();
                cmbBranchID.Text = dataGridView1.SelectedRows[0].Cells[7].Value.ToString();
                cmbDepartmentID.ResetText();
                cmbDepartmentID.Text = dataGridView1.SelectedRows[0].Cells[4].Value.ToString();
                cmbDesigId.ResetText();
                cmbDesigId.Text = dataGridView1.SelectedRows[0].Cells[5].Value.ToString();
                cmbEmpShift.ResetText();
                cmbEmpShift.Text = dataGridView1.SelectedRows[0].Cells[6].Value.ToString();

                txtAddress.Text = dataGridView1.SelectedRows[0].Cells[9].Value.ToString();
                txtPhone.Text = dataGridView1.SelectedRows[0].Cells[10].Value.ToString();
                txtMobile.Text = dataGridView1.SelectedRows[0].Cells[11].Value.ToString();
                txtEmail.Text = dataGridView1.SelectedRows[0].Cells[16].Value.ToString();
                if (dataGridView1.SelectedRows[0].Cells[13].Value.ToString() != "")
                    DateDob.Value = Convert.ToDateTime(dataGridView1.SelectedRows[0].Cells[12].Value.ToString());
                if (dataGridView1.SelectedRows[0].Cells[14].Value.ToString() != "")
                    DateJoining.Value = Convert.ToDateTime(dataGridView1.SelectedRows[0].Cells[13].Value.ToString());
                if (dataGridView1.SelectedRows[0].Cells[15].Value.ToString() != "")
                    DateResig.Value = Convert.ToDateTime(dataGridView1.SelectedRows[0].Cells[14].Value.ToString());
                cmbEmpStatus.Text = dataGridView1.SelectedRows[0].Cells[15].Value.ToString();
                
            }
            catch (Exception ex)
            {
            }
        }

        private void btnUpdate_Click(object sender, EventArgs e)
        {
            try
            { 
              
                sqlCmd = new SqlCommand();
                sqlCmd.Parameters.Clear();

                sqlCmd.CommandText = "update  EmployDetails set EmpFirstName='" + txtFName.Text
                                    + "',Empcode='" + txtEmpCode.Text
                                    + "',EmpLastName='" + txtLName.Text + "',EmpCmpId='" + "1"
                                    + "' ,EmpDesigId='" + cmbDesigId.SelectedValue.ToString()
                                    + "' ,EmpShiftid='" + cmbEmpShift.SelectedValue.ToString()
                                    + "',EmpDeptId='"
                                    + cmbDepartmentID.SelectedValue.ToString()
                                    + "' ,EmpBrId='" + cmbBranchID.SelectedValue.ToString() + "',EmpAddress='"
                                    + txtAddress.Text + "',EmpPhone='" + txtPhone.Text + "',EmpMobile='"
                                    + txtMobile.Text + "',EmpDOB='" + DateDob.Value.ToString("MM/dd/yyyy") + "',EmpJoinDate='"
                                    + DateJoining.Value.ToString("MM/dd/yyyy") + "',EmpResigDate='" + DateResig.Value.ToString("MM/dd/yyyy") + "' ,EmpStatus='"
                                    + cmbEmpStatus.SelectedValue.ToString() + "' ,EmpEmail='" + txtEmail.Text + "' where EmpId='"
                                    + txtEmpId.Text + "'";
                sqlCmd.Connection = sqlCon;
                sqlCon.Open();
                sqlCmd.ExecuteNonQuery();
                sqlCon.Close();
               MessageBox.Show("Successfuly Updated");
                groupBox2.Visible = false;
                employDetailsTableAdapter1.Fill(employeeDataset1.EmployDetails);

                dataGridView1.Refresh();
            }
            catch (Exception ex)
            {
            }
        }

        
      
    }
}
